﻿
Imports System.Data.OracleClient

Namespace persistent

    Public Class TanimsizHat

        Private Shared ConnStr As String = GlobalVar.OraConnStr

        Private Shared dbPackageName As String = "eu_bill.TANIMSIZHAT."

       

        Public Shared Function CreateCustomer(
                                              ByVal DDL_ADR_NATIONALITY As Integer,
                                              ByVal TB_CS_TC_ID As String,
                                              ByVal TB_ADR_FNAME As String,
                                              ByVal TB_ADR_LNAME As String,
                                              ByVal TB_CS_REMARK1 As String,
                                              ByVal TB_CS_BIRTH_PROVINCE As String,
                                              ByVal TB_CS_REMARK2 As String,
                                              ByVal TDD_ADR_BIRTHDT As String,
                                              ByVal TB_CS_REG_PROVINCE As String,
                                              ByVal TB_CS_REG_DISTRICT As String,
                                              ByVal DDL_ADR_SEX As String,
                                              ByVal DDL_IDTYPE_CODE As Integer,
                                              ByVal TB_ADR_IDNO As String,
                                              ByVal DLL_PRG_CODE As String,
                                              ByVal DLL_RPCODE As Integer,
                                              ByVal DLL_RS_CODE As Integer,
                                              ByVal TB_ADR_ROLES As String,
                                              ByVal DDL1_ADR_CITY As String,
                                              ByVal DDL1_ADR_NOTE1 As String,
                                              ByVal TB1_ADR_STREET As String,
                                              ByVal TB1_ADR_ZIP As String,
                                              ByVal TB1_ADR_STREETNO As String,
                                              ByVal TB_ADR_PHN1_AREA As String,
                                              ByVal TB_ADR_PHN1 As String,
                                              ByVal TB_ADR_PHN2_AREA As String,
                                              ByVal TB_ADR_PHN2 As String,
                                              ByVal TB_ADR_SMSNO As String,
                                              ByVal TB_ADR_EMAIL As String,
                                              ByVal TB_ADR_NOTE2 As String,
                                              ByVal LBL_Mernis_Status As String,
                                              ByRef MESSAGE As String) As DataTable





            Dim command As New OracleCommand(dbPackageName & "TanimsizCreateCustomer")


            command.Parameters.Add("pin_ADR_NATIONALITY", OracleType.Int32).Value = DDL_ADR_NATIONALITY
            command.Parameters.Add("pin_CS_TC_ID", OracleType.VarChar).Value = TB_CS_TC_ID
            command.Parameters.Add("pin_ADR_FNAME", OracleType.VarChar).Value = TB_ADR_FNAME
            command.Parameters.Add("pin_ADR_LNAME", OracleType.VarChar).Value = TB_ADR_LNAME
            command.Parameters.Add("pin_CS_REMARK1", OracleType.VarChar).Value = TB_CS_REMARK1
            command.Parameters.Add("pin_CS_BIRTH_PROVINCE", OracleType.VarChar).Value = TB_CS_BIRTH_PROVINCE
            command.Parameters.Add("pin_CS_REMARK2", OracleType.VarChar).Value = TB_CS_REMARK2
            command.Parameters.Add("pin_ADR_BIRTHDT", OracleType.VarChar).Value = TDD_ADR_BIRTHDT
            command.Parameters.Add("pin_CS_REG_PROVINCE", OracleType.VarChar).Value = TB_CS_REG_PROVINCE
            command.Parameters.Add("pin_CS_REG_DISTRICT", OracleType.VarChar).Value = TB_CS_REG_DISTRICT
            command.Parameters.Add("pin_ADR_SEX", OracleType.Char).Value = DDL_ADR_SEX
            command.Parameters.Add("pin_IDTYPE_CODE", OracleType.Int32).Value = DDL_IDTYPE_CODE
            command.Parameters.Add("pin_ADR_IDNO", OracleType.VarChar).Value = TB_ADR_IDNO
            command.Parameters.Add("pin_PRG_CODE", OracleType.VarChar).Value = DLL_PRG_CODE
            command.Parameters.Add("pin_RPCODE", OracleType.Int32).Value = DLL_RPCODE
            command.Parameters.Add("pin_RS_CODE", OracleType.Int32).Value = DLL_RS_CODE
            command.Parameters.Add("pin_ADR_ROLES", OracleType.VarChar).Value = TB_ADR_ROLES
            command.Parameters.Add("pin_ADR_CITY", OracleType.VarChar).Value = DDL1_ADR_CITY
            command.Parameters.Add("pin_ADR_NOTE1", OracleType.VarChar).Value = DDL1_ADR_NOTE1
            command.Parameters.Add("pin_ADR_STREET", OracleType.VarChar).Value = TB1_ADR_STREET
            command.Parameters.Add("pin_ADR_ZIP", OracleType.VarChar).Value = TB1_ADR_ZIP
            command.Parameters.Add("pin_ADR_STREETNO", OracleType.VarChar).Value = TB1_ADR_STREETNO
            command.Parameters.Add("pin_ADR_PHN1_AREA", OracleType.VarChar).Value = TB_ADR_PHN1_AREA
            command.Parameters.Add("pin_ADR_PHN1", OracleType.VarChar).Value = TB_ADR_PHN1
            command.Parameters.Add("pin_ADR_PHN2_AREA", OracleType.VarChar).Value = TB_ADR_PHN2_AREA
            command.Parameters.Add("pin_ADR_PHN2", OracleType.VarChar).Value = TB_ADR_PHN2
            command.Parameters.Add("pin_ADR_SMSNO", OracleType.VarChar).Value = TB_ADR_SMSNO
            command.Parameters.Add("pin_ADR_EMAIL", OracleType.VarChar).Value = TB_ADR_EMAIL
            command.Parameters.Add("pin_ADR_NOTE2", OracleType.VarChar).Value = TB_ADR_NOTE2
            command.Parameters.Add("pin_cs_data_level", OracleType.VarChar).Value = LBL_Mernis_Status
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)


        End Function

        Public Shared Function CreateCustomerSME(
                                             ByVal TB_ADR_NAME As String,
                                             ByVal TB_ADR_TAXNO As String,
                                             ByVal TB_ADR_COMPNO As String,
                                             ByVal TB_CS_TC_ID As String,
                                             ByVal TB_ADR_FNAME As String,
                                             ByVal TB_ADR_LNAME As String,
                                             ByVal TB_CS_REMARK1 As String,
                                             ByVal TB_CS_BIRTH_PROVINCE As String,
                                             ByVal TB_CS_REMARK2 As String,
                                             ByVal TDD_ADR_BIRTHDT As String,
                                             ByVal TB_CS_REG_PROVINCE As String,
                                             ByVal TB_CS_REG_DISTRICT As String,
                                             ByVal DDL_ADR_SEX As String,
                                             ByVal DDL_IDTYPE_CODE As Integer,
                                             ByVal TB_ADR_IDNO As String,
                                             ByVal DLL_PRG_CODE As String,
                                             ByVal DLL_RPCODE As Integer,
                                             ByVal DLL_RS_CODE As Integer,
                                             ByVal TB_ADR_ROLES As String,
                                             ByVal DDL1_ADR_CITY As String,
                                             ByVal DDL1_ADR_NOTE1 As String,
                                             ByVal TB1_ADR_STREET As String,
                                             ByVal TB1_ADR_ZIP As String,
                                             ByVal TB1_ADR_STREETNO As String,
                                             ByVal TB_ADR_PHN1_AREA As String,
                                             ByVal TB_ADR_PHN1 As String,
                                             ByVal TB_ADR_PHN2_AREA As String,
                                             ByVal TB_ADR_PHN2 As String,
                                             ByVal TB_ADR_SMSNO As String,
                                             ByVal TB_ADR_EMAIL As String,
                                             ByVal TB_ADR_NOTE2 As String,
                                             ByVal LBL_Mernis_Status As String,
                                             ByRef MESSAGE As String) As DataTable





            Dim command As New OracleCommand(dbPackageName & "TanimsizCreateCustomerSME")

            command.Parameters.Add("pin_ADR_NAME", OracleType.VarChar).Value = TB_ADR_NAME
            command.Parameters.Add("pin_ADR_TAXNO", OracleType.VarChar).Value = TB_ADR_TAXNO
            command.Parameters.Add("pin_ADR_COMPNO", OracleType.VarChar).Value = TB_ADR_COMPNO
            command.Parameters.Add("pin_CS_TC_ID", OracleType.VarChar).Value = TB_CS_TC_ID
            command.Parameters.Add("pin_ADR_FNAME", OracleType.VarChar).Value = TB_ADR_FNAME
            command.Parameters.Add("pin_ADR_LNAME", OracleType.VarChar).Value = TB_ADR_LNAME
            command.Parameters.Add("pin_CS_REMARK1", OracleType.VarChar).Value = TB_CS_REMARK1
            command.Parameters.Add("pin_CS_BIRTH_PROVINCE", OracleType.VarChar).Value = TB_CS_BIRTH_PROVINCE
            command.Parameters.Add("pin_CS_REMARK2", OracleType.VarChar).Value = TB_CS_REMARK2
            command.Parameters.Add("pin_ADR_BIRTHDT", OracleType.VarChar).Value = TDD_ADR_BIRTHDT
            command.Parameters.Add("pin_CS_REG_PROVINCE", OracleType.VarChar).Value = TB_CS_REG_PROVINCE
            command.Parameters.Add("pin_CS_REG_DISTRICT", OracleType.VarChar).Value = TB_CS_REG_DISTRICT
            command.Parameters.Add("pin_ADR_SEX", OracleType.Char).Value = DDL_ADR_SEX
            command.Parameters.Add("pin_IDTYPE_CODE", OracleType.Int32).Value = DDL_IDTYPE_CODE
            command.Parameters.Add("pin_ADR_IDNO", OracleType.VarChar).Value = TB_ADR_IDNO
            command.Parameters.Add("pin_PRG_CODE", OracleType.VarChar).Value = DLL_PRG_CODE
            command.Parameters.Add("pin_RPCODE", OracleType.Int32).Value = DLL_RPCODE
            command.Parameters.Add("pin_RS_CODE", OracleType.Int32).Value = DLL_RS_CODE
            command.Parameters.Add("pin_ADR_ROLES", OracleType.VarChar).Value = TB_ADR_ROLES
            command.Parameters.Add("pin_ADR_CITY", OracleType.VarChar).Value = DDL1_ADR_CITY
            command.Parameters.Add("pin_ADR_NOTE1", OracleType.VarChar).Value = DDL1_ADR_NOTE1
            command.Parameters.Add("pin_ADR_STREET", OracleType.VarChar).Value = TB1_ADR_STREET
            command.Parameters.Add("pin_ADR_ZIP", OracleType.VarChar).Value = TB1_ADR_ZIP
            command.Parameters.Add("pin_ADR_STREETNO", OracleType.VarChar).Value = TB1_ADR_STREETNO
            command.Parameters.Add("pin_ADR_PHN1_AREA", OracleType.VarChar).Value = TB_ADR_PHN1_AREA
            command.Parameters.Add("pin_ADR_PHN1", OracleType.VarChar).Value = TB_ADR_PHN1
            command.Parameters.Add("pin_ADR_PHN2_AREA", OracleType.VarChar).Value = TB_ADR_PHN2_AREA
            command.Parameters.Add("pin_ADR_PHN2", OracleType.VarChar).Value = TB_ADR_PHN2
            command.Parameters.Add("pin_ADR_SMSNO", OracleType.VarChar).Value = TB_ADR_SMSNO
            command.Parameters.Add("pin_ADR_EMAIL", OracleType.VarChar).Value = TB_ADR_EMAIL
            command.Parameters.Add("pin_ADR_NOTE2", OracleType.VarChar).Value = TB_ADR_NOTE2
            command.Parameters.Add("pin_cs_data_level", OracleType.VarChar).Value = LBL_Mernis_Status
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)



        End Function





        Public Shared Function CreateCustomerLATop(
                                            ByVal TB_ADR_NAME As String,
                                            ByVal TB_ADR_TAXNO As String,
                                            ByVal TB_ADR_COMPNO As String,
                                            ByVal TB_ADR_FNAME As String,
                                            ByVal TB_ADR_LNAME As String,
                                            ByVal TB_CS_REMARK1 As String,
                                            ByVal TB_CS_BIRTH_PROVINCE As String,
                                            ByVal TB_CS_REMARK2 As String,
                                            ByVal TDD_ADR_BIRTHDT As String,
                                            ByVal TB_CS_REG_PROVINCE As String,
                                            ByVal TB_CS_REG_DISTRICT As String,
                                            ByVal DDL_ADR_SEX As String,
                                            ByVal DDL_IDTYPE_CODE As Integer,
                                            ByVal TB_ADR_IDNO As String,
                                            ByVal DLL_PRG_CODE As String,
                                            ByVal DLL_RPCODE As Integer,
                                            ByVal DLL_RS_CODE As Integer,
                                            ByVal TB_ADR_ROLES As String,
                                            ByVal DDL1_ADR_CITY As String,
                                            ByVal DDL1_ADR_NOTE1 As String,
                                            ByVal TB1_ADR_STREET As String,
                                            ByVal TB1_ADR_ZIP As String,
                                            ByVal TB1_ADR_STREETNO As String,
                                            ByVal TB_ADR_PHN1_AREA As String,
                                            ByVal TB_ADR_PHN1 As String,
                                            ByVal TB_ADR_PHN2_AREA As String,
                                            ByVal TB_ADR_PHN2 As String,
                                            ByVal TB_ADR_SMSNO As String,
                                            ByVal TB_ADR_EMAIL As String,
                                            ByVal TB_ADR_NOTE2 As String,
                                            ByRef MESSAGE As String) As DataTable





            Dim command As New OracleCommand(dbPackageName & "TanimsizCreateCustomerLATop")

            command.Parameters.Add("pin_ADR_NAME", OracleType.VarChar).Value = TB_ADR_NAME
            command.Parameters.Add("pin_ADR_TAXNO", OracleType.VarChar).Value = TB_ADR_TAXNO
            command.Parameters.Add("pin_ADR_COMPNO", OracleType.VarChar).Value = TB_ADR_COMPNO
            command.Parameters.Add("pin_ADR_FNAME", OracleType.VarChar).Value = TB_ADR_FNAME
            command.Parameters.Add("pin_ADR_LNAME", OracleType.VarChar).Value = TB_ADR_LNAME
            command.Parameters.Add("pin_CS_REMARK1", OracleType.VarChar).Value = TB_CS_REMARK1
            command.Parameters.Add("pin_CS_BIRTH_PROVINCE", OracleType.VarChar).Value = TB_CS_BIRTH_PROVINCE
            command.Parameters.Add("pin_CS_REMARK2", OracleType.VarChar).Value = TB_CS_REMARK2
            command.Parameters.Add("pin_ADR_BIRTHDT", OracleType.VarChar).Value = TDD_ADR_BIRTHDT
            command.Parameters.Add("pin_CS_REG_PROVINCE", OracleType.VarChar).Value = TB_CS_REG_PROVINCE
            command.Parameters.Add("pin_CS_REG_DISTRICT", OracleType.VarChar).Value = TB_CS_REG_DISTRICT
            command.Parameters.Add("pin_ADR_SEX", OracleType.Char).Value = DDL_ADR_SEX
            command.Parameters.Add("pin_IDTYPE_CODE", OracleType.Int32).Value = DDL_IDTYPE_CODE
            command.Parameters.Add("pin_ADR_IDNO", OracleType.VarChar).Value = TB_ADR_IDNO
            command.Parameters.Add("pin_PRG_CODE", OracleType.VarChar).Value = DLL_PRG_CODE
            command.Parameters.Add("pin_RPCODE", OracleType.Int32).Value = DLL_RPCODE
            command.Parameters.Add("pin_RS_CODE", OracleType.Int32).Value = DLL_RS_CODE
            command.Parameters.Add("pin_ADR_ROLES", OracleType.VarChar).Value = TB_ADR_ROLES
            command.Parameters.Add("pin_ADR_CITY", OracleType.VarChar).Value = DDL1_ADR_CITY
            command.Parameters.Add("pin_ADR_NOTE1", OracleType.VarChar).Value = DDL1_ADR_NOTE1
            command.Parameters.Add("pin_ADR_STREET", OracleType.VarChar).Value = TB1_ADR_STREET
            command.Parameters.Add("pin_ADR_ZIP", OracleType.VarChar).Value = TB1_ADR_ZIP
            command.Parameters.Add("pin_ADR_STREETNO", OracleType.VarChar).Value = TB1_ADR_STREETNO
            command.Parameters.Add("pin_ADR_PHN1_AREA", OracleType.VarChar).Value = TB_ADR_PHN1_AREA
            command.Parameters.Add("pin_ADR_PHN1", OracleType.VarChar).Value = TB_ADR_PHN1
            command.Parameters.Add("pin_ADR_PHN2_AREA", OracleType.VarChar).Value = TB_ADR_PHN2_AREA
            command.Parameters.Add("pin_ADR_PHN2", OracleType.VarChar).Value = TB_ADR_PHN2
            command.Parameters.Add("pin_ADR_SMSNO", OracleType.VarChar).Value = TB_ADR_SMSNO
            command.Parameters.Add("pin_ADR_EMAIL", OracleType.VarChar).Value = TB_ADR_EMAIL
            command.Parameters.Add("pin_ADR_NOTE2", OracleType.VarChar).Value = TB_ADR_NOTE2
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)



        End Function

        Public Shared Function CreateCustomerLASub(
                                          ByVal customer_id_high As String,
                                          ByVal CS_LEVEL As Integer,
                                          ByVal TB_ADR_NAME As String,
                                          ByVal TB_ADR_TAXNO As String,
                                          ByVal TB_ADR_COMPNO As String,
                                          ByVal TB_ADR_FNAME As String,
                                          ByVal TB_ADR_LNAME As String,
                                          ByVal TB_CS_REMARK1 As String,
                                          ByVal TB_CS_BIRTH_PROVINCE As String,
                                          ByVal TB_CS_REMARK2 As String,
                                          ByVal TDD_ADR_BIRTHDT As String,
                                          ByVal TB_CS_REG_PROVINCE As String,
                                          ByVal TB_CS_REG_DISTRICT As String,
                                          ByVal DDL_ADR_SEX As String,
                                          ByVal DDL_IDTYPE_CODE As Integer,
                                          ByVal TB_ADR_IDNO As String,
                                          ByVal DLL_PRG_CODE As String,
                                          ByVal DLL_RPCODE As Integer,
                                          ByVal DLL_RS_CODE As Integer,
                                          ByVal TB_ADR_ROLES As String,
                                          ByVal DDL1_ADR_CITY As String,
                                          ByVal DDL1_ADR_NOTE1 As String,
                                          ByVal TB1_ADR_STREET As String,
                                          ByVal TB1_ADR_ZIP As String,
                                          ByVal TB1_ADR_STREETNO As String,
                                          ByVal TB_ADR_PHN1_AREA As String,
                                          ByVal TB_ADR_PHN1 As String,
                                          ByVal TB_ADR_PHN2_AREA As String,
                                          ByVal TB_ADR_PHN2 As String,
                                          ByVal TB_ADR_SMSNO As String,
                                          ByVal TB_ADR_EMAIL As String,
                                          ByVal TB_ADR_NOTE2 As String,
                                          ByRef MESSAGE As String) As DataTable





            Dim command As New OracleCommand(dbPackageName & "TanimsizCreateCustomerLASub")

            command.Parameters.Add("pin_customer_id_high", OracleType.Int32).Value = customer_id_high
            command.Parameters.Add("pin_CS_LEVEL", OracleType.VarChar).Value = CS_LEVEL
            command.Parameters.Add("pin_ADR_NAME", OracleType.VarChar).Value = TB_ADR_NAME
            command.Parameters.Add("pin_ADR_TAXNO", OracleType.VarChar).Value = TB_ADR_TAXNO
            command.Parameters.Add("pin_ADR_COMPNO", OracleType.VarChar).Value = TB_ADR_COMPNO
            command.Parameters.Add("pin_ADR_FNAME", OracleType.VarChar).Value = TB_ADR_FNAME
            command.Parameters.Add("pin_ADR_LNAME", OracleType.VarChar).Value = TB_ADR_LNAME
            command.Parameters.Add("pin_CS_REMARK1", OracleType.VarChar).Value = TB_CS_REMARK1
            command.Parameters.Add("pin_CS_BIRTH_PROVINCE", OracleType.VarChar).Value = TB_CS_BIRTH_PROVINCE
            command.Parameters.Add("pin_CS_REMARK2", OracleType.VarChar).Value = TB_CS_REMARK2
            command.Parameters.Add("pin_ADR_BIRTHDT", OracleType.VarChar).Value = TDD_ADR_BIRTHDT
            command.Parameters.Add("pin_CS_REG_PROVINCE", OracleType.VarChar).Value = TB_CS_REG_PROVINCE
            command.Parameters.Add("pin_CS_REG_DISTRICT", OracleType.VarChar).Value = TB_CS_REG_DISTRICT
            command.Parameters.Add("pin_ADR_SEX", OracleType.Char).Value = DDL_ADR_SEX
            command.Parameters.Add("pin_IDTYPE_CODE", OracleType.Int32).Value = DDL_IDTYPE_CODE
            command.Parameters.Add("pin_ADR_IDNO", OracleType.VarChar).Value = TB_ADR_IDNO
            command.Parameters.Add("pin_PRG_CODE", OracleType.VarChar).Value = DLL_PRG_CODE
            command.Parameters.Add("pin_RPCODE", OracleType.Int32).Value = DLL_RPCODE
            command.Parameters.Add("pin_RS_CODE", OracleType.Int32).Value = DLL_RS_CODE
            command.Parameters.Add("pin_ADR_ROLES", OracleType.VarChar).Value = TB_ADR_ROLES
            command.Parameters.Add("pin_ADR_CITY", OracleType.VarChar).Value = DDL1_ADR_CITY
            command.Parameters.Add("pin_ADR_NOTE1", OracleType.VarChar).Value = DDL1_ADR_NOTE1
            command.Parameters.Add("pin_ADR_STREET", OracleType.VarChar).Value = TB1_ADR_STREET
            command.Parameters.Add("pin_ADR_ZIP", OracleType.VarChar).Value = TB1_ADR_ZIP
            command.Parameters.Add("pin_ADR_STREETNO", OracleType.VarChar).Value = TB1_ADR_STREETNO
            command.Parameters.Add("pin_ADR_PHN1_AREA", OracleType.VarChar).Value = TB_ADR_PHN1_AREA
            command.Parameters.Add("pin_ADR_PHN1", OracleType.VarChar).Value = TB_ADR_PHN1
            command.Parameters.Add("pin_ADR_PHN2_AREA", OracleType.VarChar).Value = TB_ADR_PHN2_AREA
            command.Parameters.Add("pin_ADR_PHN2", OracleType.VarChar).Value = TB_ADR_PHN2
            command.Parameters.Add("pin_ADR_SMSNO", OracleType.VarChar).Value = TB_ADR_SMSNO
            command.Parameters.Add("pin_ADR_EMAIL", OracleType.VarChar).Value = TB_ADR_EMAIL
            command.Parameters.Add("pin_ADR_NOTE2", OracleType.VarChar).Value = TB_ADR_NOTE2
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)


        End Function


        Public Shared Function CreateContract(
                                                    ByVal v_customer_id As Integer,
                                                    ByVal DDL_RPCODE As Integer,
                                                    ByVal DDL_RS_CODE As Integer,
                                                    ByVal v_RES_ID As Integer,
                                                    ByVal v_DN_ID As Integer,
                                                    ByVal pin_rehber As String,
                                                    ByVal pin_uluslarasiArama As String,
                                                    ByVal pin_uluslarasiDolasim As String,
                                                    ByVal dealer_custcode As String,
                                                    ByVal pin_userid As String,
                                                    ByRef MESSAGE As String
                                                     ) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizCreateContract")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = v_customer_id
            command.Parameters.Add("pin_RPCODE", OracleType.Int32).Value = DDL_RPCODE
            command.Parameters.Add("pin_RS_CODE", OracleType.Int32).Value = DDL_RS_CODE
            command.Parameters.Add("pin_RES_ID", OracleType.Int32).Value = v_RES_ID
            command.Parameters.Add("pin_DN_ID", OracleType.Int32).Value = v_DN_ID
            command.Parameters.Add("pin_rehber", OracleType.VarChar).Value = pin_rehber
            command.Parameters.Add("pin_uluslararasi_arama", OracleType.VarChar).Value = pin_uluslarasiArama
            command.Parameters.Add("pin_uluslararasi_dolasim", OracleType.VarChar).Value = pin_uluslarasiDolasim
            command.Parameters.Add("pin_dealer_custcode", OracleType.VarChar).Value = dealer_custcode
            command.Parameters.Add("pin_userid", OracleType.VarChar).Value = pin_userid
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim log_custmor_id As String = Convert.ToString(v_customer_id)
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New AveaPrepaidToGPOPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", log_custmor_id, "null", "null", "Select(CreateContract)", "TanimsizHat", gen.Session("user"))

            Return ExecuteDataTable(command)


        End Function


        Public Shared Function LASubCustInfoSearch(ByVal pin_CUSTOMER_ID As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizLASubCustInfoSearch")
            command.Parameters.Add("pin_CUSTOMER_ID", OracleType.Int32).Value = pin_CUSTOMER_ID
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim log_custmor_id As String = Convert.ToString(pin_CUSTOMER_ID)
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New AveaPrepaidToGPOPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", log_custmor_id, "null", "null", "Select(LASubCustInfoSearch)", "TanimsizHat", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function

        Public Shared Function Tanimsizgetcustomertype() As DataTable

            Dim command As New OracleCommand(dbPackageName & "Tanimsizgetcustomertype")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function TanimsizgetcustomertypeSME() As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizgetcustomertypeSME")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function TanimsizgetcustomertypeLA() As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizgetcustomertypeLA")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function


        Public Shared Function KimlikType() As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizGetKimlik")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function aktivasyonType() As DataTable

            Dim command As New OracleCommand(dbPackageName & "Tanimsizaktivasyonnedeni")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function IlType() As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizAutocity")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function IlceType(ByVal city_code As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizAutodistinct")
            command.Parameters.Add("city_code", OracleType.Int32).Value = city_code
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function DealerInfo(ByVal sm_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "Tanimsizgetdealerinfo")
            command.Parameters.Add("pin_sm_id", OracleType.Int32).Value = sm_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function


        Public Shared Function SMECustCodeSearch(ByVal pin_CUSTCODE As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizSMECustSearch")
            command.Parameters.Add("pin_CUSTCODE", OracleType.VarChar).Value = pin_CUSTCODE
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New AveaPrepaidToGPOPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, pin_CUSTCODE, "null", "null", "null", "Select(SMECustCodeSearch)", "TanimsizHat", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function

        Public Shared Function LASubCustCodeSearch(ByVal pin_CUSTCODE As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizLASubCustSearch")
            command.Parameters.Add("pin_CUSTCODE", OracleType.VarChar).Value = pin_CUSTCODE
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New AveaPrepaidToGPOPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, pin_CUSTCODE, "null", "null", "null", "Select(LASubCustCodeSearch)", "TanimsizHat", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function

        Public Shared Function SMEVergiSearch(ByVal pin_CSCOMPTAXNO As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizSMEVergiSicilSearch")
            command.Parameters.Add("pin_CSCOMPTAXNO", OracleType.VarChar).Value = pin_CSCOMPTAXNO
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function



        Public Shared Function TarifeType(ByVal city_code As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizGetTariff")
            command.Parameters.Add("pin_prgcode", OracleType.Int32).Value = city_code
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function TarifeTypeSME(ByVal city_code As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizGetTariffSME")
            command.Parameters.Add("pin_prgcode", OracleType.Int32).Value = city_code
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function TarifeTypeLA(ByVal city_code As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizGetTariffLA")
            command.Parameters.Add("pin_prgcode", OracleType.Int32).Value = city_code
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function


        Public Shared Function GetCustomerInfo(ByVal pin_customer_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizGetCustomerInfo")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim log_custmor_id As String = Convert.ToString(pin_customer_id)
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New AveaPrepaidToGPOPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", log_custmor_id, "null", "null", "Select(GetCustomerInfo)", "TanimsizHat", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function
        Public Shared Function GetRatePlan(ByVal pin_co_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizGetRatePlan")
            command.Parameters.Add("pin_co_id", OracleType.Int32).Value = pin_co_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim log_coid As String = Convert.ToString(pin_co_id)
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New AveaPrepaidToGPOPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", "null", log_coid, "null", "Select(GetRatePlan)", "TanimsizHat", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function
        Public Shared Function GetCustomerCode(ByVal pin_customer_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizGetCustomerCode")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim log_customer_id As String = Convert.ToString(pin_customer_id)
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New AveaPrepaidToGPOPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", log_customer_id, "null", "null", "Select(GetCustomerCode)", "TanimsizHat", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function
        Public Shared Function GetTariffContract(ByVal customer_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizGetTariffContract")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = customer_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim log_customer_id As String = Convert.ToString(customer_id)
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New AveaPrepaidToGPOPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", log_customer_id, "null", "null", "Select(GetCustomerCode)", "TanimsizHat", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function

        Public Shared Function GetMsisdn(ByVal serialnum As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizGetMsisdn")
            command.Parameters.Add("pin_sm_serialnum", OracleType.VarChar).Value = serialnum
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function GetSIMInfo(ByVal serialnum As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizGetSIMInfo")
            command.Parameters.Add("pin_sm_serialnum", OracleType.VarChar).Value = serialnum
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function GetMsisdnInfo(ByVal MSISDN As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizGetMsisdnInfo")
            command.Parameters.Add("pin_MSISDN", OracleType.VarChar).Value = MSISDN
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function CheckSIM(ByVal serialnum As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizCheckSIM")
            command.Parameters.Add("pin_sm_serialnum", OracleType.VarChar).Value = serialnum
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function CountryType() As DataTable

            Dim command As New OracleCommand(dbPackageName & "TanimsizAutoCountry")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function GetLALevel(ByVal pin_customer_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetLASubCustomerLevel")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim log_customer_id As String = Convert.ToString(pin_customer_id)
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New AveaPrepaidToGPOPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", log_customer_id, "null", "null", "Select(GetLALevel)", "TanimsizHat", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function

        Public Shared Function GetDealerInfo(ByVal pin_SIM_NO As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetDealerInfo")
            command.Parameters.Add("pin_SIM_NO", OracleType.VarChar).Value = pin_SIM_NO
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function GetRPServiceStatus(ByVal pin_tmcode As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetRPServiceStatus")
            command.Parameters.Add("pin_tmcode", OracleType.Int32).Value = pin_tmcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function
        Public Shared Function GetRPCodeInfo(ByVal pin_customer_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetRPCodeInfo")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function GetCustomerLevelWithCustcode(ByVal pin_custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustomerLevelWithCustcode")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = pin_custcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function


        Public Shared Function DealerControl(ByVal pin_custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "DealerControl")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = pin_custcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function ExecuteNonQuery(ByVal command As OracleCommand, ByRef MESSAGE As String)
            Dim connection As New OracleConnection(ConnStr)
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure

            Try
                connection.Open()
                command.ExecuteNonQuery()
            Catch ex As Exception
                MESSAGE = ex.Message.ToString
            Finally
                connection.Close()
            End Try
        End Function
        Public Shared Function ExecuteDataTable(ByVal command As OracleCommand, Optional ByVal directSql As Boolean = 0) As DataTable
            Dim connection As New OracleConnection(ConnStr)
            Dim dataAdapter As New OracleDataAdapter(command)
            Dim dataTable As New DataTable
            Dim dataSet As New DataSet

            command.Connection = connection

            If Not (directSql) Then
                command.CommandType = CommandType.StoredProcedure
            End If

            Try
                connection.Open()
                dataAdapter.Fill(dataSet)
                dataTable = dataSet.Tables(0)
            Catch ex As Exception
            Finally
                connection.Close()
                dataSet.Dispose()
            End Try

            Return dataTable
        End Function




    End Class
End Namespace